﻿Imports System.Data
Imports System.Data.SqlClient
Public Class PrestadoresDAL
    Dim cmd As SqlCommand
#Region "Métodos"
    Public Function InserirAlterar(ByVal precodigo As Integer, ByVal predescricao As String, ByVal ccccodigo As Integer,
        ByVal preobs As String, ByVal tpecodigo As Integer, ByVal precpf As String, ByVal prerg As String, ByVal precnpj As String,
        ByVal preinscricao As String, ByVal pretelres As String, ByVal pretelcom As String, ByVal pretelcel As String, ByVal pretelfax As String,
        ByVal preend As String, ByVal prenumero As String, ByVal prebairro As String, ByVal precep As String, ByVal precidade As String, ByVal estuf As String,
        ByVal precomplemento As String, ByVal preemail As String, ByVal stacod As Integer)
        Dim cmd = New SqlCommand
        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SPPrestadores"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = precodigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = predescricao
            cmd.Parameters.Add(New SqlParameter("@ccccod", SqlDbType.Int)).Value = ccccodigo
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = preobs
            cmd.Parameters.Add(New SqlParameter("@tppessoa", SqlDbType.Int)).Value = tpecodigo
            cmd.Parameters.Add(New SqlParameter("@cpf", SqlDbType.VarChar)).Value = precpf
            cmd.Parameters.Add(New SqlParameter("@rg", SqlDbType.VarChar)).Value = prerg
            cmd.Parameters.Add(New SqlParameter("@cnpj", SqlDbType.VarChar)).Value = precnpj
            cmd.Parameters.Add(New SqlParameter("@inscricao", SqlDbType.VarChar)).Value = preinscricao
            cmd.Parameters.Add(New SqlParameter("@telres", SqlDbType.VarChar)).Value = pretelres
            cmd.Parameters.Add(New SqlParameter("@telcom", SqlDbType.VarChar)).Value = pretelcom
            cmd.Parameters.Add(New SqlParameter("@telcel", SqlDbType.VarChar)).Value = pretelcel
            cmd.Parameters.Add(New SqlParameter("@telfax", SqlDbType.VarChar)).Value = pretelfax
            cmd.Parameters.Add(New SqlParameter("@endereco", SqlDbType.VarChar)).Value = preend
            cmd.Parameters.Add(New SqlParameter("@numero", SqlDbType.VarChar)).Value = prenumero
            cmd.Parameters.Add(New SqlParameter("@bairro", SqlDbType.VarChar)).Value = prebairro
            cmd.Parameters.Add(New SqlParameter("@cep", SqlDbType.VarChar)).Value = precep
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = precidade
            cmd.Parameters.Add(New SqlParameter("@uf", SqlDbType.VarChar)).Value = estuf
            cmd.Parameters.Add(New SqlParameter("@complemento", SqlDbType.VarChar)).Value = precomplemento
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = preemail
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = stacod
            'cmd.Parameters.Add(New SqlParameter("@dtnascimento", SqlDbType.DateTime)).Value = Format(dtnascimento, "yyyy/MM/dd")
            precodigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return precodigo
    End Function
#End Region
#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_FinPrestadores where pre_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_FinPrestadores where pre_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_FinPrestadores where pre_cpf = @descricao and sta_codigo <> 3"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.BigInt)).Value = Val(Descricao)
                Case 3
                    cmd.CommandText = "select * from vw_FinPrestadores where pre_cnpj= @descricao sta_codigo <> 3"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.BigInt)).Value = Val(Descricao)
                Case 4
                    cmd.CommandText = "select * from vw_FinPrestadores where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from vw_FinPrestadores where sta_codigo <> 3 order by pre_descricao"
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Valida Exclusão"
    Function validaExclusao(ByVal descricao As String, ByVal stat As Integer) As Boolean
        Dim tab As DataTable
        Try

            If (stat <> 1) Then
                cmd = New SqlCommand
                cmd.CommandText = "select * from Financeiro.Ofi_ContasPagar where ctp_fornprestador COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao "
                cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                tab = objconexao.executaConsulta(cmd)

                If (tab.Rows.Count = 0) Then
                    Return True 'não encontrou
                Else
                    Return False 'encontrou
                End If

            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return True
    End Function
#End Region
End Class
